package idv.arthur.work;

import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

public class QueryPersonnel extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public QueryPersonnel() {
        super();
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    	doPost(request,response);
    }

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		Widgets wid = new Widgets();
		request.setCharacterEncoding( wid.UIcodeSet );
		response.setContentType("application/json");
		response.setCharacterEncoding( wid.UIcodeSet );
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		String errPars = "}";
		
		String strSQL = "SELECT COUNT(userid) FROM personnel";
		
		ResultTableSet resultTableSet = new ResultTableSet();
		
		PersonnelPartition pp = new PersonnelPartition();
		ArrayList<InfoStruc> notIn = null;
		ArrayList<InfoStruc> in = null;
		
		/**
		 * type=2
		 * 	畫面personnel要用
		 * 
		 * type=1
		 * 	畫面 roleAssign要用
		 * 		回傳兩份資訊
		 * 		a.	這個子系統下有那些管理者
		 * 		b. 所有人員扣掉a後的名單
		 * */
		
		int type = 2;
		int roleType = 0;
		if ( request.getParameter("fc") !=null ) {
			roleType = Integer.parseInt( request.getParameter("roleType") );
			type = Integer.parseInt( request.getParameter("fc") );
		}
		
		String sord = request.getParameter("sord");
		String sidx =  request.getParameter("sidx");
		
		int page = 1;
		int total_pages = 1;
		int count = 0;
		int limit = 6;

		if ("".equalsIgnoreCase(sidx)|| sidx==null) {
			sidx = "t1.userid";
		} 
		if ("".equalsIgnoreCase(sord)|| sord==null) {
			sord = "ASC";
		} else {
			page = Integer.parseInt(request.getParameter("page"));
			limit = Integer.parseInt(request.getParameter("rows"));
		}
		
		try {
			conn = wid.getConn(true);
			ps = conn.prepareStatement(strSQL);
			rs = ps.executeQuery();
			rs.next();
			count = rs.getInt(1);

			if (count != 0) {

				if (limit < count) {
					total_pages = (count / limit) + 1;
				}
				
				if ( conn.getMetaData().getDatabaseProductName().contains( "MySQL" ) ) {
					strSQL = "SELECT t1.userid,t1.phonenum,t1.pwd,t1.username,t1.email FROM personnel t1 LEFT JOIN mproleuser t2 ON  WHERE t1.userid=t2.userid AND t2.subsysid='"+request.getParameter("subSysId")+"' ORDER BY "+sidx+" "+sord+" LIMIT "+(limit*page-limit)+","+ limit;	
				} else if ( conn.getMetaData().getDatabaseProductName().contains( "Informix" ) ) {
					strSQL = "SELECT SKIP "+(limit*page-limit)+" FIRST "+ limit +" t1.userid,t1.phonenum,t1.pwd,t1.username,t1.email FROM personnel t1, OUTER mproleuser t2 WHERE t1.userid=t2.userid AND t2.subsysid='"+request.getParameter("subSysId")+"' ORDER BY "+sidx+" "+sord;
				}
				
				
				if ( type ==1 ) {
					/*	可將變數初始化	*/
					notIn = new ArrayList<InfoStruc>();
					in = new ArrayList<InfoStruc>();
					strSQL = "SELECT t1.userid,t1.username FROM personnel t1, mproleuser t2 WHERE t1.userid=t2.userid AND t2.subsysid='"+request.getParameter("subSysId")+"' AND t2.roleId="+roleType;
				}
				
				ps = conn.prepareStatement(strSQL);
				
				rs = ps.executeQuery();
				ArrayList<Rows> rows = new ArrayList<Rows>();

				strSQL = "SELECT t2.roleId,t2.roleName FROM mproleuser t1,roleList t2 WHERE t1.userId=? AND t1.roleId=t2.roleId ORDER BY roleName DESC";
				ps = conn.prepareStatement(strSQL);
				while (rs.next()) {
					String userid = rs.getString("userid");
					
					Rows row = new Rows();
					
					if ( type ==1 ) {
						InfoStruc infoStruc = new InfoStruc();
						infoStruc.setId( userid );
						infoStruc.setName( wid.DB2UI( rs.getString("username"),conn.getMetaData().getDatabaseProductName() ) );
						in.add(infoStruc);	
					} else {
						row.setId(userid);
						String[] cell = new String[7];
						cell[0] = userid;
						cell[1] = wid.DB2UI( rs.getString("username"),conn.getMetaData().getDatabaseProductName() );
						cell[2] = rs.getString("phonenum");
						cell[3] = rs.getString("email");
						cell[4] = rs.getString("pwd");
						cell[6] = ""; 
								
						ps.setString(1, userid);
						rs2 = ps.executeQuery();
						while ( rs2.next() ) {
							cell[6] += wid.DB2UI( rs2.getString("roleName"),conn.getMetaData().getDatabaseProductName() ) +"("+rs2.getInt("roleId")+")\n";
						}
						
						if ( !"".equalsIgnoreCase(cell[6])) {
							cell[6] = cell[6].substring(0, cell[6].length()-1);
						}
						
						row.setCell(cell);
						rows.add(row);
					}
				}
				
				if ( type ==1 ) {
					//接著取得 非這個子系統管理員的名單
					strSQL = "SELECT userid,username FROM personnel WHERE userid NOT IN (SELECT userid FROM mproleuser WHERE bssid=? AND subsysid=?  AND roleId="+roleType+")";
					ps = conn.prepareStatement(strSQL);
					ps.setString(1, "MBMS" );
					ps.setString(2, request.getParameter("subSysId") );
					rs = ps.executeQuery();
					
					while ( rs.next() ) {
						InfoStruc infoStruc = new InfoStruc();
						infoStruc.setId( rs.getString("userid") );
						infoStruc.setName( wid.DB2UI( rs.getString("username"),conn.getMetaData().getDatabaseProductName() ) );
						notIn.add(infoStruc);
					}
					
					pp.setIn(in);				//指定子系統的管理員清單
					pp.setNotIn(notIn);	//非這個子系統管理員的名單
				}
				
				
				resultTableSet.setRows(rows);
			} else {
				page = 0;
				total_pages = 0;
			}
			resultTableSet.setPage(String.valueOf(page));
			resultTableSet.setTotal(total_pages);
			resultTableSet.setRecords(String.valueOf(count));
			
		} catch ( SQLException e) {
			wid.kem(conn, ps, e, wid.toEType("SQLException")+errPars, wid.isPrint2Console );
		} catch ( UnsupportedEncodingException e) {
			wid.kem(conn, ps, e, wid.toEType("UnsupportedEncodingException")+errPars, wid.isPrint2Console );
		} catch ( Exception e) {
			wid.kem(conn, ps, e, wid.toEType("Exception")+errPars, wid.isPrint2Console );
		} finally {
			try {
				if (rs != null) {rs.close();}
				if (rs2 != null) {rs2.close();}
				if (ps!= null) {ps.close();}
				if (conn!= null) {conn.close();}
			} catch (SQLException e) {
				System.out.println(wid.errProcess(e));
			}
		}
		if ( type ==1 ) {
			response.getWriter().write(new Gson().toJson( pp ));
		} else {
			response.getWriter().write(new Gson().toJson(resultTableSet));	
		}
	}
}